﻿CREATE PROCEDURE [dbo].[GetSaleProperty]
@SaleType NVARCHAR (3), @PriceMax BIGINT=0, @PriceMin BIGINT=0, @Project NVARCHAR (30)='', @FloorSize INT=0, @LandSize INT=0, @Location NVARCHAR (30)='', @BestBuy INT=2, @offset INT=0, @next INT=20, @FloorSizeMin INT=0, @LandSizeMin INT=0
AS
BEGIN

SET NOCOUNT ON;
set @BestBuy = 1 - @BestBuy;

IF @SaleType='SR'
select a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.MediumUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy, 
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt
,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.MediumUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
--and b.statusdelete = 0 and c.statusdelete=0 
--and d.statusdelete = 0 and e.statusdelete = 0 
and f.statusdelete = 0
and (a.SRYes = 1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and (a.SRAmt <= @PriceMax or @PriceMax = 0)
and (a.SRAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or 
@FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or  h.Project like '%' + @Project + '%')
and a.BestBuy <> @BestBuy
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name
, f.mobile,f.email,f.MediumUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition,
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY

ELSE IF @SaleType = 'SL'
select a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.MediumUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.MediumUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join 
aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join 
aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SLYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and (a.SLAmt <= @PriceMax or @PriceMax = 0)
and (a.SLAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or  h.Project like '%' + @Project + '%')
and a.BestBuy <> @BestBuy
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.MediumUrl, a.LASF, a.BUSF,h.Project,a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure
,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY

ELSE IF @SaleType = 'SI'
select a.propertyid,a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.MediumUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case

when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
 when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else
a.UpdateDate end as UpdatedDate,max(g.MediumUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid

left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SIYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and (a.SIAmt <= @PriceMax or @PriceMax = 0)
and (a.SIAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' 
or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' 
or d.State like '%' + @Project + '%' or  h.Project like '%' + @Project + '%')
and a.BestBuy <> @BestBuy
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.MediumUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark
order by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY

ELSE IF @SaleType = 'SC'
select a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, 
f.mobile,f.email,f.MediumUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 
then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
'' as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.MediumUrl) as 
PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 

and f.statusdelete = 0
and (a.SCYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and (a.SCAmt <= @PriceMax or @PriceMax = 0)
and (a.SCAmt >= @PriceMin  or @PriceMin = 0)
and (a.BUSF <= @FloorSize or @FloorSize = 0)
and (a.BUSF >= @FloorSizeMin or @FloorSizeMin = 0)
and (a.LASF <= @LandSize or @LandSize = 0)
and (a.LASF >= @LandSizeMin or @LandSizeMin = 0)
and (@Location = '' or  h.Project like '%' + @Location + '%' or c.Location like '%' + @Location + '%')
and (@Project = '' or b.Road like '%' + @Project + '%' or c.Location like '%' + @Project + '%' or d.State like '%' + @Project + '%' or  h.Project like '%' + @Project + '%')
and a.BestBuy <> @BestBuy
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location
, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.MediumUrl, a.LASF, a.BUSF,h.Project,

a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate,e.TypeDesc,a.SaleRemark
order 
by a.UpdateDate desc,a.CreateDate desc
--OFFSET @offset ROWS FETCH NEXT @next ROWS ONLY
ELSE
select 'WRONG INPUT PARAMETER(SR/SL/SC/SI)' AS NORESULT from Mobile_Settings
END
SELECT @BestBuy AS bestbuycount
